/*
 * Group:PrePool K9-357 
 * 8/2010
 */
package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import core.Dictionary;
import util.Controller;
import dto.DictionaryDTO;
import dto.ManagerDictDTO;
import dao.UsersDAO;

/**
 *
 */
public class ManagerInDictDAO {
	public static Logger LOG = Logger.getLogger("dao.ManagerInDictDAO");
	private UsersDAO userDAO = new UsersDAO();
	private WordDAO wordDAO = new WordDAO();
	private DictionariesDAO dictDAO = new DictionariesDAO();
	private DictionaryDTO dictDTO = new DictionaryDTO();
	private Dictionary dict = null;

	/**
	 * add new dictionary manager
	 * 
	 * @param dictId
	 *            dictionary's Id
	 * @param managerId
	 *            manager's Id
	 * @param role
	 *            manager's role in this dictionary
	 * @return true if successful,false if not
	 */
	public static boolean addManager(int dictId, int managerId, int role) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement cstat = null;
		PreparedStatement pstatCheck = null;
		ResultSet rs = null;

		try {
			cnn = Controller.getConnection();
			pstatCheck = cnn
					.prepareStatement("select * from m_managerindict where m_dictID="
							+ dictId + " and m_userID=" + managerId);
			cstat = cnn.prepareCall("{call m_managerindict_insert(?, ?, ?)}");
			cstat.setInt(1, managerId);
			cstat.setInt(2, dictId);
			cstat.setInt(3, role);
			rs = pstatCheck.executeQuery();

			if (!rs.next()) { // insert new manager if not exist
				rs.close();
				pstatCheck.close();
				bOK = (cstat.executeUpdate() == 1);
			} else {
				if (rs != null) {
					rs.close();
				}
				if (pstatCheck != null) {
					pstatCheck.close();
				}
			}
		} catch (Exception ex) {
			// LOG.error("addManager", ex);
		} finally {
			try {
				if (cstat != null) {
					cstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return bOK;
	}

	/**
	 * remove dictionary manager
	 * 
	 * @param dictId
	 *            ditionary's Id
	 * @param managerId
	 *            manager's Id
	 * @return true if successful,false if not
	 */
	public static boolean removeManager(int dictId, int managerId) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement cstatDelMana = null;

		try {
			cnn = Controller.getConnection();
			// delete from m_managerindict table
			cstatDelMana = cnn
					.prepareCall("{call m_managerindict_delete(?,?)}");
			cstatDelMana.setInt(1, managerId);
			cstatDelMana.setInt(2, dictId);
			cstatDelMana.executeUpdate();
			cstatDelMana.close();
			bOK = true;
		} catch (Exception ex) {
			// LOG.error("removeManager", ex);
			bOK = false;
		} finally {
			try {
				if (cstatDelMana != null) {
					cstatDelMana.close();
				}
			} catch (Exception ex) {
			}
		}
		return bOK;
	}

	/**
	 * 
	 * @param dictID
	 * @param managerID
	 * @return
	 */
	public static boolean changeRole(int dictID, int managerID) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement pstat = null;

		try {
			cnn = Controller.getConnection();
			// 1: senior manager, 2: junior manager
			pstat = cnn.prepareCall("{call update_managerindict_role(?,?)}");
			pstat.setInt(1, managerID);
			pstat.setInt(2, dictID);
			bOK = (pstat.executeUpdate() == 1);
		} catch (Exception ex) {
			// LOG.error("changeRole", ex);
		} finally {
			try {
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return bOK;
	}

	/**
	 * @param username
	 * @param manager
	 * @return
	 */
	public List<ManagerDictDTO> getManagement(String username, int manager) {
		List<ManagerDictDTO> listManagerindict = new ArrayList<ManagerDictDTO>();
		int userId = userDAO.getUserIDByName(username);
		Connection cnn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select * from m_managerindict where m_userid= "
							+ userId + "and m_role = " + manager);
			rs = pstat.executeQuery();
			while (rs.next()) {
				dictDTO = dictDAO.getDictionary(rs.getInt("m_dictID"));
				dict = new Dictionary(dictDTO.getM_dicturl(), dictDTO
						.getM_delCount());
				ManagerDictDTO managerdict = new ManagerDictDTO();
				managerdict.setM_dictID(rs.getInt("m_dictID"));
				managerdict.setM_userID(rs.getInt("m_userID"));
				managerdict.setReviewer(this.getNumberReviewer(rs
						.getInt("m_dictID")));
				managerdict.setDictName(dict.getIfoFile().getStrBookname());
				if (manager == 1) {
					managerdict.setCountnewword(wordDAO.getNumSuggestedWord(rs
							.getInt("m_dictID")));
				} else {
					managerdict.setCountnewword(wordDAO.getNumReviewedWord(rs
							.getInt("m_dictID")));
				}
				listManagerindict.add(managerdict);
			}
		} catch (Exception ex) {
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}

		if (listManagerindict.size() == 0)
			return null;
		else
			return listManagerindict;
	}

	/**
	 * 
	 * @param username
	 *            manager's name
	 * @param dictId
	 *            dictionary's Id
	 * @return manager's role in the dictionary
	 */
	public int getRole(String username, int dictId) {
		Connection cnn = null;
		PreparedStatement pstat = null;
		int role = 0;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select m_role from m_managerindict where m_dictID = ? and m_userID in (select m_id from m_users where m_username = ?)");
			pstat.setInt(1, dictId);
			pstat.setString(2, username);

			ResultSet rs = pstat.executeQuery();
			rs.next();

			role = rs.getInt("m_role");
		}

		catch (Exception e) {
			// TODO: handle exception
		}

		return role;
	}

	/**
	 * @param dictId
	 *            dictionary's Id
	 * @return number of reviewers in the dictionary
	 */
	public int getNumberReviewer(int dictId) {
		Connection cnn = null;
		PreparedStatement pstat = null;
		int count = 0;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select count(*) as num from m_managerindict where m_dictID = ? AND m_role = ?");
			pstat.setInt(1, dictId);
			pstat.setInt(2, 1);
			ResultSet rs = pstat.executeQuery();
			rs.next();

			count = rs.getInt("num");
		}

		catch (Exception e) {
			// TODO: handle exception
		}

		return count;
	}

	/**
	 * Check if manager have the role(reviewer, approver)
	 * 
	 * @param username
	 *            manager's name
	 * @param dictId
	 *            dictinary's Id
	 * @param role
	 * @return
	 */
	public boolean isManager(String username, int dictId, int role) {

		Connection cnn = null;
		PreparedStatement pstat = null;
		boolean valid = false;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select m_role from m_managerindict where m_dictID = ? "
							+ "and m_userID in (select m_id from m_users where m_username = ?)");
			pstat.setInt(1, dictId);
			pstat.setString(2, username);
			ResultSet rs = pstat.executeQuery();
			rs.next();
			valid = (role == rs.getInt("m_role"));
		}

		catch (Exception e) {
			// TODO: handle exception
		}

		return valid;
	}
}
